﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data.SqlClient;
using System.Configuration;
using System.Data;
using System.IO;
using System.Xml;
using System.Text;
/// <summary>
/// Summary description for interviewerMethord
/// </summary>
public class InterviewerMethord
{
	public InterviewerMethord()
	{
		//
		// TODO: Add constructor logic here
		//
	}
    public void connOpen()
    {
        if (conn.State == ConnectionState.Closed)
        {
            conn.Open();
        }
    }
    public void connClose()
    {
        if (conn.State == ConnectionState.Open)
        {
            conn.Close();
        }
    }
    SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["connection"].ConnectionString);
    public DataTable ShowAllApplicant(string _FulName)
    {
        DataTable data = new DataTable();
        try
        {
            connOpen();
            SqlCommand sqlcmm = new SqlCommand("EXECUTE InterviewerSearchApplicant @FullName", conn);
            sqlcmm.Parameters.AddWithValue("@FullName", _FulName);

            SqlDataAdapter da = new SqlDataAdapter(sqlcmm);
            DataSet ds = new DataSet();
            da.Fill(ds);
            data = ds.Tables[0];
        }
        catch (Exception ex)
        {

            Console.WriteLine(ex.Message);
        }
        finally
        {
            connClose();
        }
        return data;
    }

    public DataTable Searchinterview(string _date)
    {
        DataTable searchtb = new DataTable();
        try
        {
            SqlCommand sqlcmm = new SqlCommand("EXECUTE SearchInterview @DateInterview", conn);
            sqlcmm.Parameters.AddWithValue("@DateInterview", _date);
            connOpen();
            SqlDataAdapter sqlda = new SqlDataAdapter(sqlcmm);
            DataSet ds = new DataSet();
            sqlda.Fill(ds);
            searchtb = ds.Tables[0];
        }
        catch (Exception ex)
        {

            Console.Write(ex.Message);
        }
        finally {
            connClose();
        }
        return searchtb;
    }
    public DataTable ViewallInterview()
    {
        DataTable tb = new DataTable();
        try
        {
            SqlCommand sqlcmm = new SqlCommand("select * from vw_AllInterview", conn);
            SqlDataAdapter da = new SqlDataAdapter(sqlcmm);
            if(conn.State == ConnectionState.Closed){
                conn.Open();
            }
            DataSet ds = new DataSet();
            da.Fill(ds);
            tb = ds.Tables[0];
        }
        catch (Exception ex)   
        {

            Console.Write(ex.Message);
        }
       
        return tb;
    }
        
    public DataTable ViewVacancy(string _VID) 
    {
        DataTable ViewTb = new DataTable();
        try
        {
            SqlCommand sqlcom = new SqlCommand("EXECUTE viewVacancy @VID", conn);
            sqlcom.Parameters.AddWithValue("@VID", _VID);
            conn.Open();
            SqlDataAdapter da = new SqlDataAdapter(sqlcom);
            DataSet ds = new DataSet();
            da.Fill(ds);
            ViewTb = ds.Tables[0];
        }
        catch (Exception ex)
        {

            Console.Write(ex.Message);
        }
        finally {
            connClose();
        }
        return ViewTb;
    }

    public DataTable ViewApplicant(string _AppID) {
        DataTable apptb = new DataTable();
        try
        {
            SqlCommand com = new SqlCommand("EXECUTE viewApplicant @ApplicantID", conn);
            com.Parameters.AddWithValue("@ApplicantID", _AppID);
            conn.Open();
            SqlDataAdapter sqlda = new SqlDataAdapter(com);
            DataSet ds = new DataSet();
            sqlda.Fill(ds);
            apptb = ds.Tables[0];
        }
        catch (Exception ex)
        {

            Console.Write(ex.Message);
        }
        finally {
            connClose();
        }
        return apptb;
    
    }

    //Page InterviewerViewInterviewInformation
    public DataSet ReturnAllInterviewInforFollowInterviewID(string _User)
    {
        DataSet ds = new DataSet();
        try
        {
            SqlCommand comm = new SqlCommand("EXECUTE ReturnAllIterviewInformationFollowInterViewID @User", conn);
            comm.Parameters.AddWithValue("@User", _User);
            connOpen();
            SqlDataAdapter da = new SqlDataAdapter(comm);
            da.Fill(ds);
        }
        catch (Exception ex)
        {
        }
        finally
        {
            connClose();
        }
        return ds;
    }

    public DataSet ReturnDataSearchInterview(string _TextSearch, string _TypeSearch, string _User)
    {
        DataSet ds = new DataSet();
        try
        {
            SqlCommand comm = null;
            switch (_TypeSearch)
            {
                case "VacancyID":
                    comm = new SqlCommand("EXECUTE InterviewerSearchVacancyID @ID, @TextSearch", conn);
                    comm.Parameters.AddWithValue("@ID", _User);
                    comm.Parameters.AddWithValue("@TextSearch", _TextSearch);
                    break;
                case "ApplicantID":
                    comm = new SqlCommand("EXECUTE InterviewerSearchApplicantID @ID, @TextSearch", conn);
                    comm.Parameters.AddWithValue("@ID", _User);
                    comm.Parameters.AddWithValue("@TextSearch", _TextSearch);
                    break;
                case "Applicant Name":
                    comm = new SqlCommand("EXECUTE InterviewerSearchApplicantName @ID, N'" + _TextSearch + "'", conn);
                    comm.Parameters.AddWithValue("@ID", _User);
                    break;
                case "Date Interview":
                    int Year = Convert.ToDateTime(_TextSearch).Year;
                    int Month = Convert.ToDateTime(_TextSearch).Month;
                    int Day = Convert.ToDateTime(_TextSearch).Day;
                    string date = "" + Year;
                    if (Month < 10)
                        date += "-0" + Month;
                    else
                        date += "-" + Month;
                    if (Day < 10)
                        date += "-0" + Day;
                    else
                        date += "-" + Day;
                    comm = new SqlCommand("EXECUTE InterviewerSearchDateInterview @ID, @TextSearch", conn);
                    comm.Parameters.AddWithValue("@ID", _User);
                    comm.Parameters.AddWithValue("@TextSearch", date);
                    break;
                case "Status":
                    comm = new SqlCommand("EXECUTE InterviewerSearchStatus @ID, @TextSearch", conn);
                    comm.Parameters.AddWithValue("@ID", _User);
                    comm.Parameters.AddWithValue("@TextSearch", _TextSearch);
                    break;
            }
            connOpen();
            SqlDataAdapter da = new SqlDataAdapter(comm);
            da.Fill(ds);
        }
        catch (Exception ex)
        {
        }
        finally
        {
            connClose();
        }
        return ds;
    }
    public string ReturnNameFollowID(string _ID, string _Table)
    {
        string FullName = "";
        try
        {
            SqlCommand comm = null;
            if (_Table.Equals("Intervewer"))
            {
                comm = new SqlCommand("EXECUTE ReturnNameInterviewer @ID", conn);
            }
            else
            {
                comm = new SqlCommand("EXECUTE ReturnNameApplicant @ID", conn);
            }
            comm.Parameters.AddWithValue("@ID", _ID);
            connOpen();
            SqlDataReader dr = comm.ExecuteReader();
            while (dr.Read())
            {
                FullName = dr.GetString(0);
            }
        }
        catch (Exception ex)
        {
        }
        finally
        {
            connClose();
        }
        return FullName;
    }

    //Page VacancyManager
    public DataSet ReturnAllVacancyInformation(string _User)
    {
        DataSet ds = new DataSet();
        try
        {
            SqlCommand comm = new SqlCommand("EXECUTE ReturnAllVacancyFollowInterviewerID @User", conn);
            comm.Parameters.AddWithValue("User", _User);
            connOpen();
            SqlDataAdapter da = new SqlDataAdapter(comm);
            da.Fill(ds);
        }
        catch (Exception ex)
        {
        }
        finally
        {
            connClose();
        }
        return ds;
    }
    public DataSet ReturnVacancyInforFollowSearch(string _User, string _TypeSearch, string _TextSearch)
    {
        DataSet ds = new DataSet();
        try
        {
            SqlCommand comm = null;
            if (_TypeSearch.Equals("VacancyID"))
            {
                comm = new SqlCommand("EXECUTE InterReturnVacancyInforFollowSearchID @User, @ID", conn);
            }
            else
            {
                comm = new SqlCommand("EXECUTE InterReturnVacancyInforFollowSearchName @User, @ID", conn);
            }
            comm.Parameters.AddWithValue("@User", _User);
            comm.Parameters.AddWithValue("@ID", _TextSearch);
            connOpen();
            SqlDataAdapter da = new SqlDataAdapter(comm);
            da.Fill(ds);
        }
        catch (Exception ex)
        {
        }
        finally
        {
            connClose();
        }
        return ds;
    }


    //Page HiredApplicant
    public DataTable ReturnInterviewInformation(string _ID)
    {
        DataTable tb = null;
        try
        {
            SqlCommand comm = new SqlCommand("EXECUTE ReturnInterviewInfor @ID", conn);
            comm.Parameters.AddWithValue("@ID", _ID);
            connOpen();
            SqlDataAdapter da = new SqlDataAdapter(comm);
            DataSet ds = new DataSet();
            da.Fill(ds);
            tb = ds.Tables[0];
        }
        catch (Exception ex)
        {
        }
        finally
        {
            connClose();
        }
        return tb;
    }
    public string ReturnStatusApplicantFollowID(string _ID)
    {
        string Status = "";
        try
        {
            SqlCommand comm = new SqlCommand("EXECUTE ReturnStatusApplicantFollowID @ID", conn);
            comm.Parameters.AddWithValue("@ID", _ID);
            connOpen();
            SqlDataReader dr = comm.ExecuteReader();
            while (dr.Read())
            {
                Status = dr.GetInt16(0).ToString();
            }
        }
        catch (Exception ex)
        {
        }
        finally
        {
            connClose();
        }
        return Status;
    }
    public bool HiredApplicantForVacancy(string _ApplicantID, int _Interview)
    {
        bool Result = false;
        try
        {
            int iresult;
            SqlCommand com = new SqlCommand("EXECUTE HiredApplicantInsert @ID, 1", conn);
            com.Parameters.AddWithValue("@ID", _Interview);
            connOpen();
            iresult = com.ExecuteNonQuery();
            if (iresult > 0)
            {
                SqlCommand comm = new SqlCommand("EXECUTE HiredApplicant @ID", conn);
                comm.Parameters.AddWithValue("@ID", _ApplicantID);
                connOpen();
                iresult = comm.ExecuteNonQuery();
                if (iresult > 0)
                {
                    Result = true;
                }
            }
        }
        catch (Exception ex)
        {
        }
        finally
        {
            connClose();
        }
        return Result;
    }
    public bool UpdateStatusApplicant(string _Applicant, int _Interview)
    {
        bool result = false;
        try
        {
            int iresult;
            SqlCommand com = new SqlCommand("EXECUTE HiredApplicantInsert @ID, 1", conn);
            com.Parameters.AddWithValue("@ID", _Interview);
            connOpen();
            iresult = com.ExecuteNonQuery();
            if (iresult > 0)
            {
                SqlCommand comm = new SqlCommand("EXECUTE UpdateStatusApplicant @ID, 4", conn);
                comm.Parameters.AddWithValue("@ID", _Applicant);
                connOpen();
                int check = comm.ExecuteNonQuery();
                if (check > 0)
                {
                    result = true;
                }
            }
        }
        catch (Exception ex)
        {
        }
        finally
        {
            connClose();
        }
        return result;
    }
    public string ReturnEmailFollowApplicantID(string _ID)
    {
        string Email = "";
        try
        {
            SqlCommand comm = new SqlCommand("EXECUTE ReturnEmailFollowID @ID", conn);
            comm.Parameters.AddWithValue("@ID", _ID);
            connOpen();
            SqlDataReader dr = comm.ExecuteReader();
            while (dr.Read())
            {
                Email = dr.GetString(0);
            }
        }
        catch (Exception ex)
        {
        }
        finally
        {
            connClose();
        }
        return Email;
    }

    public DataTable ReturnInformationInterview(string _ID)
    {
        DataTable tb = new DataTable();
        try
        {
            SqlCommand comm = new SqlCommand("EXECUTE ReturnInterviewInfor @ID", conn);
            comm.Parameters.AddWithValue("@ID", _ID);
            connOpen();
            SqlDataAdapter da = new SqlDataAdapter(comm);
            DataSet ds = new DataSet();
            da.Fill(ds);
            tb = ds.Tables[0];
        }
        catch (Exception ex)
        {
        }
        finally
        {
            connClose();
        }
        return tb;
    }

    //Page Interviewer proposed interview
    public string ReturnHRFollowInterviewID(string _ID)
    {
        string HRID = "";
        try
        {
            SqlCommand comm = new SqlCommand("EXECUTE ReturnHRFollowInterviewID @ID", conn);
            comm.Parameters.AddWithValue("@ID", _ID);
            connOpen();
            SqlDataReader dr = comm.ExecuteReader();
            while (dr.Read())
            {
                HRID = dr.GetString(0);
            }
        }
        catch (Exception ex)
        {
        }
        finally
        {
            connClose();
        }
        return HRID;
    }
}